with tab as (
   select 
      waybill_no
     ,network_code
     ,send_later_scantime      as send_scantime
     ,send_later_scanuser
     ,loading_send_next_network_name
     ,arrival_early_scantime   as arrival_scantime
     ,arrival_early_scanuser
     ,unloading_arrival_previous_network_code
     ,deliver_later_scantime   as deliver_scantime
     ,deliver_later_scanuser
     ,actual_sign_scantime     as sign_scantime
     ,actual_sign_scanuser 
   from jms_dwd.dwd_s01_whole_operations_basic_agg_dt
   where dt between '{{ execution_date | cst_ds }}' and date_add('{{ execution_date | cst_ds }}',13)
   and ( send_later_scantime    between '{{ execution_date | cst_ds }} 00:00:00' and '{{ execution_date | cst_ds }} 23:59:59' or 
         arrival_early_scantime between '{{ execution_date | cst_ds }} 00:00:00' and '{{ execution_date | cst_ds }} 23:59:59' or 
         deliver_later_scantime between '{{ execution_date | cst_ds }} 00:00:00' and '{{ execution_date | cst_ds }} 23:59:59' or 
         actual_sign_scantime   between '{{ execution_date | cst_ds }} 00:00:00' and '{{ execution_date | cst_ds }} 23:59:59' )
),
way as (
   select 
      waybill_no
     ,input_network_code
     ,waybill_input_time
     ,collect_staff_name
     ,package_collect_weight 
   from jms_dwd.dwd_waybill_order_index_dt
    where dt between '{{ execution_date | cst_ds }}' and date_add('{{ execution_date | cst_ds }}',13)
     and waybill_input_time between '{{ execution_date | cst_ds }} 00:00:00' and '{{ execution_date | cst_ds }} 23:59:59'
     and is_delete = 1
     and is_void = 0
),
network as (
   select 
      name
     ,code
     ,provider_desc
     ,city_desc
     ,area_desc
     ,agent_code
     ,agent_name
     ,franchisee_code
     ,franchisee_name
   from jms_dim.dim_sys_network_detail_dt
   where dt = '{{ execution_date | cst_ds }}'
)
insert overwrite table jms_dwd.dwd_all_operation_cnt_detail_dt partition(dt='{{ execution_date | cst_ds }}')
   select 
      tab.waybill_no                      as waybill_no              -- 运单号
     ,tab.network_code                    as network_code            -- 网点code
     ,net.name                            as network_name            -- 网点名称
     ,way.waybill_input_time              as input_time              -- 运单录入时间
     ,tab.send_scantime                   as send_scantime           -- 发件时间
     ,tab.arrival_scantime                as arrival_scantime        -- 到件时间
     ,tab.deliver_scantime                as deliver_scantime        -- 派件时间
     ,tab.sign_scantime                   as sign_scantime           -- 签收时间
     ,way.collect_staff_name              as collect_staff_name      -- 揽件业务员
     ,tab.send_later_scanuser             as send_scanuser           -- 发件业务员
     ,tab.arrival_early_scanuser          as arrival_scanuser        -- 到件业务员
     ,tab.deliver_later_scanuser          as deliver_scanuser        -- 派件业务员
     ,tab.actual_sign_scanuser            as sign_scanuser           -- 签收业务员
     ,tab.loading_send_next_network_name  as next_network_name       -- 下一站网点名称
     ,net_arrival.name                    as previous_network_name   -- 上一站网点名称
     ,way.package_collect_weight          as package_collect_weight  -- 收件重量
     ,net.provider_desc                   as provider_desc           -- 省
     ,net.city_desc                       as city_desc               -- 市
     ,net.area_desc                       as area_desc               -- 区县
     ,net.agent_code                      as agent_code              -- 代理区code
     ,net.agent_name                      as agent_name              -- 代理区名称
     ,net.franchisee_code                 as franchisee_code         -- 加盟商编码
     ,net.franchisee_name                 as franchisee_name         -- 加盟商名称
   from tab 
      full outer join way 
   on tab.waybill_no=way.waybill_no and tab.network_code=way.input_network_code
      left join network net
   on tab.network_code = net.code  
      left join network net_arrival
   on coalesce(tab.unloading_arrival_previous_network_code,tab.waybill_no) = net_arrival.code  ;